package com.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.dao.StaffStatisticsDao;
import com.excel.ExcelUtil;
import com.pojo.StaffStatistics;
import com.util.StringUtil;
import com.util.ValidateHelper;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class StaffStatisticsService extends ServiceImpl<StaffStatisticsDao, StaffStatistics> {

    @Autowired
    private StaffStatisticsDao staffStatisticsDao;

    /**
     * 查询当天员工统计
     * @return
     */
    public StaffStatistics getNowDateStaffStatistics(String staffId) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String nowDate = sdf.format(new Date());

        QueryWrapper q = new QueryWrapper();
        q.eq("staff_id", staffId);
        q.eq("access_date", nowDate);
        q.last("limit 1");
        StaffStatistics staffStatistics = staffStatisticsDao.selectOne(q);
        if (null != staffStatistics) {
            return staffStatistics;
        }
        return null;
    }


    /**
     * 获取员工统计列表
     * @param page
     * @param limit
     * @return
     */
    public IPage<StaffStatistics> getStaffStatisticsList(String name, String start_time, String end_time, Integer page, Integer limit) {
        Page p = new Page(page,limit);
        QueryWrapper<StaffStatistics> query = new QueryWrapper();
        final String name1 = name ;
        if(ValidateHelper.isNotEmptyString(name)) {
            query.and(q -> {
                q.like("staff_name" , name1).or().like("staff_bh",name1);
            }) ;
        }
        if (StringUtil.isNotNull(start_time)) {
            query.ge("access_date", start_time);
        }
        if (StringUtil.isNotNull(end_time)) {
            query.le("access_date", end_time);
        }
        query.orderByDesc("access_date");
        query.orderByDesc("access_count");
        IPage iPage = staffStatisticsDao.selectPage(p, query);
        return iPage;
    }


    /**
     * 获取员工统计列表
     * @return
     */
    public List<StaffStatistics> getStaffStatisticsByExport(String name, String start_time, String end_time) {
        QueryWrapper<StaffStatistics> query = new QueryWrapper();
        final String name1 = name ;
        if(StringUtil.isNotNull(name)) {
            query.and(q -> {
                q.like("staff_name" , name1).or().like("staff_bh",name1);
            }) ;
        }
        if (StringUtil.isNotNull(start_time)) {
            query.ge("access_date", start_time);
        }
        if (StringUtil.isNotNull(end_time)) {
            query.le("access_date", end_time);
        }
        query.orderByDesc("access_date");
        query.orderByDesc("access_count");
        List<StaffStatistics> staffStatisticsList = staffStatisticsDao.selectList(query);
        return staffStatisticsList;
    }


    public void exportExcelByStaffStatistics(String fileName, String sheetName, List resultList) throws Exception {
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd") ;
        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("员工姓名");
        titles.add("员工编号");
        titles.add("日期");
        titles.add("访问商户数");


        Label label=null;

        int iMaxLines = 60000;
        int totalLines = resultList.size();
        int page = totalLines / iMaxLines;
        int lastLines = totalLines % iMaxLines;//尾页要写的行数
        if (totalLines == 0) {
            return;
        }
        if (page >= 1 && lastLines > 0) {
            page = page + 1;
        }
        if(page == 0){
            page = 1;
        }

        for(int p = 0; p < page; p ++){
            WritableSheet sheet = workbook.createSheet(sheetName + (p + 1), p);
            int iStartRow = 0;
            int iEndRow = 0;
            if (p == 0) {
                iStartRow = 0;
                if (page == 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            } else {
                iStartRow = p * iMaxLines;
                if (p == page - 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            }
            List listPageValue = new ArrayList();//保存每页的数据
            for (int i = iStartRow; i <= iEndRow; i++) {
                if (resultList.get(i) == null) {
                    continue;
                }
                listPageValue.add(resultList.get(i));
            }

            WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
            titleStyle.setBackground(Colour.SEA_GREEN);
            titleStyle.setAlignment(Alignment.CENTRE);
            titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < titles.size(); i ++){
                //x,y,第一行的列名
                label=new Label(i,0,titles.get(i),titleStyle);
                //7：添加单元格
                sheet.addCell(label);
            }

            WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
            colmStyle.setAlignment(Alignment.CENTRE);
            colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < listPageValue.size(); i ++){
                StaffStatistics staffStatistics = (StaffStatistics) listPageValue.get(i);
                label=new Label(0,i + 1, String.valueOf(i + 1),colmStyle);
                sheet.addCell(label);
                label=new Label(1,i + 1, staffStatistics.getStaff_name(), colmStyle);
                sheet.addCell(label);
                label=new Label(2,i + 1, staffStatistics.getStaff_bh(),colmStyle);
                sheet.addCell(label);
                String curDate = "";
                if (null != staffStatistics.getAccess_date()) {
                    curDate = sdf.format(staffStatistics.getAccess_date());
                }
                label=new Label(3,i + 1, curDate, colmStyle);
                sheet.addCell(label);
                label=new Label(4,i + 1, String.valueOf(staffStatistics.getAccess_count()), colmStyle);
                sheet.addCell(label);
            }
            ExcelUtil.setColumnAutoSize(sheet,10);
        }

        workbook.write();
        workbook.close();
    }
}
